package jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import models.Company;

public class DbUtilsTest {
    public static void main(String[] args) throws ClassNotFoundException, IOException {
        try {
            System.out.println("*********DbUtils*********");
            /**
             * 导入包：commons-dbutils-1.4.jar
             */

            BasicDataSource ds = new BasicDataSource();
            // 配置DataSource
            ds.setDriverClassName("org.sqlite.JDBC");
            ds.setUrl("jdbc:sqlite:./test.db");
            QueryRunner qr = new QueryRunner(ds);

            /**
             * ResultSetHandler： 手动封装：调用ResultSetHandler的实现类中的handle方法来讲resultset的数据封装到对象中
             */
            Company company = qr.query("SELECT * FROM COMPANY where id=12", new ResultSetHandler<Company>() {

                @Override
                public Company handle(ResultSet rs) throws SQLException {
                    if (rs.next()) {
                        Company mocel = new Company();
                        mocel.setAddress(rs.getString("Address"));
                        mocel.setAge(rs.getInt("Age"));
                        mocel.setId(rs.getInt("Id"));
                        mocel.setName(rs.getString("Name"));
                        mocel.setSalary(rs.getDouble("Salary"));
                        return mocel;
                    }
                    return null;
                }

            });
            System.out.println(company);

            /**
             * BeanHandler: 自动封装数据：根据传入的BeanHandler对象来处理数据
             */
            Company company1 = qr.query("SELECT * FROM COMPANY where id=12", new BeanHandler<Company>(Company.class));
            System.out.println(company1);

            /**
             * 常见ResultSetHandler实现类: BeanHandler BeanListHandler ScalarHandler ArrayHandler
             * ArrayListHandler
             */
            // update 方法可操作增、删、改
            int result = qr.update("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (?,\"czj15\",?,\"\",?)", 15,
                    23, 2700);
            Company company2 = qr.query("SELECT * FROM COMPANY where id=15", new BeanHandler<Company>(Company.class));
            System.out.println(company2);

            company2.setId(16);
            company2.setName("czj16");
            result = qr.update("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (?,?,?,?,?)", company2);

            // DbUtils.close(conn);
            // DbUtils.commitAndClose(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
